#include "userservice.h"
#include <qsqlquery.h>
#include <QDateTime>
#include "autoDatabase.h"

UserService::UserService()
{

}

bool UserService::createTable()
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 数据库查询
    QSqlQuery query(autoDb.getDb());
    return query.exec(QString("CREATE TABLE IF NOT EXISTS t_user("
                              "`id` varchar(32) NOT NULL COMMENT '用户编码',"
                              "`name` varchar(32) NOT NULL COMMENT '用户姓名',"
                              "`gender` int DEFAULT 0 COMMENT '用户性别',"
                              "`idCard` varchar(32) DEFAULT NULL COMMENT '证件号码',"
                              "`image` varchar(255) DEFAULT NULL COMMENT '正脸照片',"
                              "`enabled` int DEFAULT 1 COMMENT '禁用状态',"
                              "`startTime` datetime default NULL COMMENT '开始时间',"
                              "`endTime` datetime default NULL COMMENT '截止时间',"
                              "`createTime` datetime default now() COMMENT '添加时间',"
                              "`reserver1` varchar(128) default NULL COMMENT '保留字段',"
                              "`reserver2` varchar(128) default NULL COMMENT '保留字段',"
                              "primary key(`id`),"
                              "key `idx_user_idcard`(`idCard`),"
                              "key `idx_user_name`(`name`)"
                            ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息';"));
}

bool UserService::add(UserInfo user)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 数据库查询
    QString sql = "insert into t_user(`id`, `name`, `gender`, `idCard`, `image`, `enabled`, `startTime`, `endTime`)"
                  "values(:id, :name, :gender, :idCard, :image, :enabled, :startTime, :endTime)";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":id", user.id);
    query.bindValue(":name", user.name);
    query.bindValue(":gender", user.gender);
    query.bindValue(":idCard", user.idCard);
    query.bindValue(":image", user.image);
    query.bindValue(":enabled", user.enabled);
    query.bindValue(":startTime", user.startTime);
    query.bindValue(":endTime", user.endTime);
    return query.exec();
}

bool UserService::remove(QString id)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 移除数据记录
    QString sql = "delete from t_user where `id`=:id";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":id", id);
    return query.exec();
}

bool UserService::update(UserInfo user)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 更新数据记录
    QString sql = "update t_user set `name`=:name, `gender`=:gender, `idCard`=:idCard,"
                  "`image`=:image,`enabled`=:enabled,`startTime`=:startTime, `endTime`=:endTime"
                  "where id=:id";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":name", user.name);
    query.bindValue(":gender", user.gender);
    query.bindValue(":idCard", user.idCard);
    query.bindValue(":image", user.image);
    query.bindValue(":enabled", user.enabled);
    query.bindValue(":startTime", user.startTime);
    query.bindValue(":endTime", user.endTime);
    query.bindValue(":id", user.id);
    return query.exec();
}

bool UserService::find(UserInfo user)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 查询数据记录
    QString sql = "select * from t_user where `idCard`=:idCard";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);
    query.bindValue(":idCard", user.idCard);

    // 打印查询结果
    if (query.exec() && query.next()) {
        user.id = query.value("id").toString();
        user.name = query.value("name").toString();
        user.gender = query.value("gender").toInt();
        user.idCard = query.value("idCard").toString();
        user.image = query.value("image").toString();
        user.enabled = query.value("enabled").toInt();
        user.startTime = query.value("startTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
        user.endTime = query.value("endTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
        return true;
    }
    return false;
}

bool UserService::findAll(QVector<UserInfo>& users)
{
    // 获取数据连接
    AutoDatabase autoDb;

    // 查询数据记录
    QString sql = "select * from t_user";
    QSqlQuery query(autoDb.getDb());
    query.prepare(sql);

    // 执行结果查询
    bool success = query.exec();
    if (success) {
        // 打印查询结果
        while (query.next()) {
            UserInfo user;
            user.id = query.value("id").toString();
            user.name = query.value("name").toString();
            user.gender = query.value("gender").toInt();
            user.idCard = query.value("idCard").toString();
            user.image = query.value("image").toString();
            user.enabled = query.value("enabled").toInt();
            user.startTime = query.value("startTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            user.endTime = query.value("endTime").toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            users.push_back(user);
        }
    }
    return success;
}
